﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.Sql;
using Microsoft.Win32;
using Microsoft.SqlServer.Management.Smo;
using System.Security.Principal;
using System.IO;
using System.Xml;
using System.Xml.Linq;
using WaterBill.DTO;

namespace WaterBill.DAL
{
    class clsConnection
    {
        string STR_CONN = @"Data Source=(local)\SQLEXPRESS;Integrated Security=True";
        string FILE_NAME = Application.StartupPath + "\\Sinfo.xml";
        protected SqlConnection sqlconn;
        SqlCommand sqlcmd = new SqlCommand();

        static string _SERVER_NAME;
        static string _DATABASE_NAME;
        public clsConnection()
        {
        }

        public Boolean checkConnectServer(string ServerName)
        {
            sqlconn = new SqlConnection();
            try
            {
                STR_CONN = @"Data Source=" + ServerName + ";Integrated Security=True";
                sqlconn.ConnectionString = STR_CONN;
                if (sqlconn.State != ConnectionState.Closed)
                {
                    sqlconn.Close();
                }
                sqlconn.Open();
            }
            catch
            {
                return false;
            }
            return true;
        }

        //Kiem tra file Sinfo.xml co ton tai hay khong
        public Boolean checkExistFileSinfo()
        {
            if (File.Exists(FILE_NAME))
            {
                DTO_InfoConnect infoconnect = ReadFileSinfo();
                _SERVER_NAME = infoconnect.ServerName;
                _DATABASE_NAME = infoconnect.DatabaseName;
                return true;
            }
            return false;
        }

        //Doc du lieu tu file Sinfo.xml
        public DTO_InfoConnect ReadFileSinfo()
        {
            DTO_InfoConnect InfoConnect = new DTO_InfoConnect();
            if (FILE_NAME.EndsWith(".xml"))
            {
                XElement xDoc = XElement.Load(FILE_NAME);
                InfoConnect = (from q in xDoc.Elements("InfoConnect")
                                       select new DTO_InfoConnect
                                       {
                                           ServerName = q.Element("ServerName").Value,
                                           DatabaseName = q.Element("DatabaseName").Value,
                                       }).FirstOrDefault();
            }
            return InfoConnect;
        }

        //Ghi du lieu vao file Sinfo.xml
        public Boolean WriteFileSinfo(string ServerName, string DatabaseName)
        {
            try
            {
                XDocument xmlDoc = new XDocument(
                    new XElement("Sinfo",
                        new XElement("InfoConnect",
                            new XElement("ServerName", ServerName),
                            new XElement("DatabaseName", DatabaseName)
                        )
                     )
                 );
                xmlDoc.Save(FILE_NAME);
            }
            catch
            {
                return false;
            }
            return true;
        }

        //Tao co so du lieu
        public Boolean createDatabase(string ServerName, string DatabaseName)
        {
            try
            {
                if (checkConnectServer(ServerName))
                {
                    sqlcmd.CommandText = "CREATE DATABASE " + DatabaseName;
                    sqlcmd.CommandType = CommandType.Text;
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.ExecuteNonQuery();
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return true;
        }

        public Boolean dropDatabase(string ServerName, string DatabaseName)
        {
            try
            {
                if (checkConnectServer(ServerName))
                {
                    sqlcmd.CommandText = "DROP DATABASE " + DatabaseName;
                    sqlcmd.CommandType = CommandType.Text;
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.ExecuteNonQuery();
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return true;
        }

        //Khoi tao co so du lieu
        public Boolean settingConnection(string ServerName, string DatabaseName)
        {
            try
            {
                Boolean flag = false;
                if (WriteFileSinfo(ServerName, DatabaseName))
                {
                    flag = true;
                    if (checkExistFileSinfo())
                    {
                        if (checkNotExistDatabase())
                        {
                            if (createDatabase(ServerName, DatabaseName))
                            {
                                if (RestoreDatabase(Application.StartupPath + "\\WaterBill.bak"))
                                {
                                    ExcuteQuery("GetAllUser");
                                    flag = true;
                                }
                                else
                                {
                                    if (dropDatabase(ServerName, DatabaseName))
                                    {
                                        File.Delete(FILE_NAME);                                           
                                    }
                                    flag = false;
                                }
                            }
                            else flag = false;
                        }
                    }
                    else flag = false;
                }
                if (flag)
                {
                    return true;
                }
            }
            catch
            {
            }
            finally
            {
                Dispose();
            }
            return false;
        }

        //Kiem tra khong ton tai cua co so du lieu trong may chu
        public Boolean checkNotExistDatabase(string ServerName = null, string DatabaseName = null)
        {
            if (ServerName == null && DatabaseName == null)
            {
                ServerName = _SERVER_NAME;
                DatabaseName = _DATABASE_NAME;
            }
            var InfoConnect = (from q in GetListDatabase(ServerName) where q.DatabaseName == DatabaseName select new DTO_InfoConnect { DatabaseName = q.DatabaseName }).Count();
            if (InfoConnect == 0)
            {
                return true;
            }
            return false;
        }
        //Lay danh sach server
        public List<DTO_InfoConnect> GetListServerName()
        {
            List<DTO_InfoConnect> obj = new List<DTO_InfoConnect>();
            try
            {
                RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
                String[] instances = (String[])rk.GetValue("InstalledInstances");
                if (instances.Length > 0)
                {
                    string server = string.Empty;
                    foreach (String element in instances)
                    {
                        if (element == "MSSQLSERVER")
                        {
                            server = "(local)";
                        }
                        else
                        {
                            server = "(local)\\"+element;
                        }
                        obj.Add(new DTO_InfoConnect()
                        {
                            ServerName = server
                        });
                    }
                }
            }
            catch
            {
            }           
            return obj;
        }
        
        //Lay danh sach database dua vao server
        public List<DTO_InfoConnect> GetListDatabase(string ServerName)
        {
            List<DTO_InfoConnect> InfoConnect = new List<DTO_InfoConnect>();
            try
            {
                if (this.checkConnectServer(ServerName))
                {
                    SqlDataAdapter da = new SqlDataAdapter("SELECT name FROM SYS.DATABASES", sqlconn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    foreach (DataRow item in dt.Rows)
                    {
                        InfoConnect.Add(new DTO_InfoConnect()
                        {
                            DatabaseName = item[dt.Columns["name"]].ToString()
                        });
                    }
                }
            }
            catch
            {
            }
            
            return InfoConnect;
        }
        //Ket noi csdl
        public Boolean connectDatabase()
        {
            sqlconn = new SqlConnection();
            try
            {
                STR_CONN = @"Data Source="+ _SERVER_NAME +"; Initial Catalog="+ _DATABASE_NAME +"; Integrated Security=True";
                sqlconn.ConnectionString = STR_CONN;
                if (sqlconn.State != ConnectionState.Closed)
                {
                    sqlconn.Close();
                }
                sqlconn.Open();
                return true;
            }
            catch
            {
                XtraMessageBox.Show("Không thể kết nối với Server.", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

        }
        protected void Dispose()
        {
            try
            {
                sqlconn.Close();
                sqlconn.Dispose();
                sqlcmd.Dispose();
                sqlcmd.Parameters.Clear();
            }
            catch
            {
            }
        }

        public void AddParameter(SqlParameter para)
        {
            try
            {
                sqlcmd.Parameters.Add(para);
            }
            catch
            {
            }
            
        }

        //Thuc thi lenh select
        protected DataTable ExcuteQuery(string ProcedureName)
        {
            DataTable dt = new DataTable();

            try
            {
                if (connectDatabase())
                {
                    sqlcmd.CommandText = ProcedureName;
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.Connection = sqlconn;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                }
            }
            catch
            {
            }
            finally
            {
                this.Dispose();
            }
            return dt;
        }

        //Thuc thi lenh them sua xoa
        protected Boolean ExcuteNonQuery(string ProcedureName)
        {
            try
            {
                if (connectDatabase())
                {
                    sqlcmd.CommandText = ProcedureName;
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.ExecuteNonQuery();
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return true;
        }

        protected int ExecuteScalar(string ProcedureName)
        {
            int temp = 0;
            try
            {
                if (connectDatabase())
                {
                    sqlcmd.CommandText = ProcedureName;
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.Connection = sqlconn;
                    SqlParameter returnValueParam = sqlcmd.Parameters.Add("@return_value", SqlDbType.Int);
                    returnValueParam.Direction = ParameterDirection.ReturnValue;
                    sqlcmd.ExecuteScalar();
                    temp = (int)returnValueParam.Value;
                }
            }
            catch
            {
            }
            finally
            {
                this.Dispose();
            }
            return temp;
        }

        protected DataTable ReadDataFromExcelFile(string path)
        {
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path.Trim() + ";Extended Properties=Excel 8.0";
            // Tạo đối tượng kết nối
            OleDbConnection oledbConn = new OleDbConnection(connectionString);
            DataTable data = null;
            DataTable dtWorkSheet = null;
            DataTable dtExcelsheetName = new DataTable();
            try
            {
                // Mở kết nối
                oledbConn.Open();
                dtWorkSheet = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dtWorkSheet.Rows.Count > 0)
                {
                    // Tạo đối tượng OleDBCommand và query data từ sheet có tên "Sheet1"
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + dtWorkSheet.Rows[0]["TABLE_NAME"].ToString() + "]", oledbConn);

                    // Tạo đối tượng OleDbDataAdapter để thực thi việc query lấy dữ liệu từ tập tin excel
                    OleDbDataAdapter oleda = new OleDbDataAdapter();

                    oleda.SelectCommand = cmd;


                    // Tạo đối tượng DataSet để hứng dữ liệu từ tập tin excel
                    DataSet ds = new DataSet();

                    // Đổ đữ liệu từ tập excel vào DataSet
                    oleda.Fill(ds);

                    data = ds.Tables[0];
                }
            }
            catch
            {    
            }
            finally
            {
                // Đóng chuỗi kết nối
                oledbConn.Close();
            }
            return data;
        }
        
        //Restore database
        public Boolean RestoreDatabase(string path)
        {
            try
            {
                if (connectDatabase())
                {
                    sqlcmd.CommandText = @"ALTER DATABASE "+_DATABASE_NAME+" SET OFFLINE WITH ROLLBACK IMMEDIATE USE master RESTORE DATABASE "+_DATABASE_NAME+" FROM DISK = N'"+path+"' WITH REPLACE ALTER DATABASE "+_DATABASE_NAME+" SET ONLINE";
                    sqlcmd.CommandType = CommandType.Text;
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.ExecuteNonQuery();
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return true;
        }
    }
}
